package br.com.citta.dao.impl;

import br.com.citta.business.entities.Empenho;
import br.com.citta.business.entities.MovimentoEmpenho;
import br.com.citta.business.entities.wrapper.EmpenhoWrapper;
import br.com.citta.dao.EmpenhoDao;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class EmpenhoDaoImpl extends HibernateDaoSupport implements EmpenhoDao {
    
    private String dataFim(String anoBase, String mesFim){
        Integer dia = (new GregorianCalendar(Integer.parseInt(anoBase), Integer.parseInt(mesFim)-1,1)).getActualMaximum(Calendar.DAY_OF_MONTH);
        return ""+dia+"/"+mesFim+"/"+anoBase+"";
    }
    
    private Calendar strToCalendar(String data) throws ParseException{
        Calendar c = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        c.setTime(sdf.parse(data));
        return c;
    }
    
    public List<Empenho> findAll(String ordem){

        return getHibernateTemplate().find("from Empenho");
        
    }
    
    public List<Empenho> findEmpenhos(String codigo, String anoBase, String mesFim){

        StringBuilder sb = new StringBuilder();
        sb.append("from Empenho where id.planoDespesa.id.codigo = '").append(codigo).append("'");
        sb.append(" and id.planoDespesa.id.unidadeGestora.id.anoBase = ").append(anoBase);
        sb.append(" and dataEmpenho <= '").append(dataFim(anoBase, mesFim)).append("'");
        sb.append(" order by numero ");
        List<Empenho> lista = getHibernateTemplate().find(sb.toString());
        return lista;
    }
    
    public List<EmpenhoWrapper> findEmpLiqPag(String codigo, String anoBase, String mesFim){
        List<Empenho> empenhos = findEmpenhos(codigo, anoBase, mesFim);
        
//        StringBuilder sb = new StringBuilder();
//        sb.append("select operacao, sum(valorOperacao) valor");
//        sb.append(" from movimentoEmpenho where id.anoBase = ").append(anoBase);
//        sb.append(" and dataMovim <= ").append(dataFim(anoBase, mesFim));
//        sb.append(" and operacao <> 'Empenho'");

        List<EmpenhoWrapper> lista = new ArrayList<>();
        EmpenhoWrapper wrapper;
            
        for (Empenho e : empenhos) {
            wrapper = new EmpenhoWrapper();

            StringBuilder numero = new StringBuilder();
            numero.append(String.format("%02d", e.getId().getPlanoDespesa().getId().getUnidadeGestora().getId().getCodigo())).append("-");
            numero.append(e.getId().getPlanoDespesa().getId().getUnidadeGestora().getId().getAnoBase()).append("/");
            numero.append(e.getId().getNumero());

            wrapper.setNumero(numero.toString());
            wrapper.setRecurso(String.format("%04d",e.getId().getPlanoDespesa().getRecVinculado().getId().getCodigo()));
            wrapper.setCredor(e.getCredor().getNome());
            wrapper.setDocumento(e.getCredor().getCnpjCpf());
            wrapper.setEmpenhado(e.getValorEmpenho());
            wrapper.setEmpenho(e);

//            StringBuilder sql = new StringBuilder();
//            sql.append(sb.toString());
//            sql.append(" and numero = ").append(e.getNumero().substring(e.getNumero().indexOf("/")+1, e.getNumero().length()));
//            SQLQuery query = getSession().createSQLQuery(sql.toString());
//            query.addScalar("operacao",Hibernate.STRING);
//            query.addScalar("valor",Hibernate.DOUBLE);

            Calendar dtIni = null;
            Calendar dtFim = null;
            try {
                dtIni = strToCalendar("01/01/"+anoBase);
                dtFim = strToCalendar(dataFim(anoBase, mesFim));
            } catch (ParseException ex) {
                Logger.getLogger(EmpenhoDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            Criteria c = getSession().createCriteria(MovimentoEmpenho.class);
            c.add(Restrictions.between("dataMovim", dtIni, dtFim));
            c.setProjection(Projections.groupProperty("operacao"));
            c.setProjection(Projections.sum("valorOperacao"));
            //c.add(Restrictions.eq("id.numero", e.getNumero().substring(e.getNumero().indexOf("/")+1, e.getNumero().length())));

            //add(Restrictions.eq("id.anoBase", anoBase)).
            //add(Restrictions.eq("id.numero", e.getNumero().substring(e.getNumero().indexOf("/")+1, e.getNumero().length()))).

            
            List list = c.list();
            for (Object m : list){
                System.out.println(m.toString());
            }

            
            lista.add(wrapper);
        }
        
        return lista;
    }
}
